﻿using System;
using System.Collections.Generic;
using System.Text;
//new
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using DataRecv.tools;
using System.Collections;
using JRCore.protocol;

namespace DataRecv.db
{
    /// <summary>
    /// Accesss数据库操作集合
    /// </summary>
    public class DBHelper
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private static OleDbConnection m_connection = null;

        /// <summary>
        /// 自己
        /// </summary>
        private static DBHelper m_db;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBHelper()
        {
            string str = string.Format("Provider=MicroSoft.Jet.OLEDB.4.0;Data Source={0}db\\access.mdb", AppDomain.CurrentDomain.BaseDirectory);

            //64位连接字符串
            //string str = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}db\\access.mdb", AppDomain.CurrentDomain.BaseDirectory);

            //32位连接字符串
            //string str = string.Format("Provider=MicroSoft.Jet.OLEDB.12.0;Data Source={0}db\\access.mdb", AppDomain.CurrentDomain.BaseDirectory);
            
            m_connection = new OleDbConnection(str);
        }

        ~DBHelper()
        {
            Dispose();
        }

        /// <summary>
        /// 创建实例
        /// </summary>
        /// <returns></returns>
        public static DBHelper GetInstance()
        {
            if (m_db == null)
            {
                m_db = new DBHelper();
                return m_db;
            }
            else
            {
                return m_db;
            }
        }

        /// <summary>
        /// 增加一条记录到数据库
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="columns">列名，用逗号分开</param>
        /// <param name="values">对应列的值，全部要求string类型</param>
        /// <returns>结果</returns>
        public bool Add(string tablename, string columns, string[] values)
        {
            bool ans = false;
            try
            {
                if (values != null)
                {
                    string strSql = "insert into " + tablename + " (" + columns + ") values(";

                    for (int i = 0; i < values.Length; i++)
                    {
                        if (i == values.Length - 1)
                        {
                            strSql += ("'" + values[i] + "')");
                        }
                        else
                        {
                            strSql += ("'" + values[i] + "',");
                        }
                    }
                    ans = ExecuteNonQuery(strSql);
                }
            }
            catch (Exception er)
            {
                ans = false;
                LogTool.log.Info("DBHelper.Add()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 查询所有系统参数
        /// </summary>
        /// <returns>参数表</returns>
        public Hashtable SelectSystemParam()
        {
            Hashtable ans = new Hashtable();
            DataTable dt = null;
            try
            {
                dt = Select("SystemParam_t", "ParamName,ParamValue", "1=1");
                if ((dt != null) && (dt.Rows != null) && (dt.Rows.Count > 0))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        ans.Add(row["ParamName"], row["ParamValue"]);
                    }
                }
            }
            catch (Exception er)
            {
                ans = null;
                if (dt != null) dt.Dispose();
                LogTool.log.Info("DBHelper.SelectSystemParam()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="columns">要查询的字段名，用逗号分开</param>
        /// <param name="condition">查询条件</param>
        /// <returns>数据表</returns>
        public DataTable Select(string tablename, string columns, string condition)
        {
            DataTable ans = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter oda = null;
            try
            {
                string strSql = "select " + columns + " from " + tablename + " where " + condition;
                if (m_connection != null)
                {
                    cmd = m_connection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSql;
                    if (m_connection.State != ConnectionState.Open) m_connection.Open();
                    oda = new OleDbDataAdapter(cmd);
                    ans = new DataTable();
                    oda.Fill(ans);
                    oda.Dispose();
                    cmd.Dispose();
                }
            }
            catch (Exception er)
            {
                ans = null;
                if (cmd != null) cmd.Dispose();
                if (oda != null) oda.Dispose();
                LogTool.log.Info("DBHelper.Select()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 修改数据库
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="columns">要修改的字段名，用逗号分开</param>
        /// <param name="values">对应字段的值</param>
        /// <param name="condition">满足的条件</param>
        /// <returns>结果</returns>
        public bool Edit(string tablename, string columns, string[] values, string condition)
        {
            bool ans = false;
            try
            {
                string[] ss = columns.Split(',');
                if ((values != null) && (ss != null) && (ss.Length == values.Length))
                {
                    string strSql = "update " + tablename + " set ";
                    for (int i = 0; i < ss.Length; i++)
                    {
                        if (i == ss.Length - 1)
                        {
                            strSql += (ss[i] + "='" + values[i] + "' where ");
                        }
                        else
                        {
                            strSql += (ss[i] + "='" + values[i] + "',");
                        }
                    }
                    strSql += condition;
                    ans = ExecuteNonQuery(strSql);
                }
            }
            catch (Exception er)
            {
                ans = false;
                LogTool.log.Info("DBHelper.Edit()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 修改系统参数,没有时则添加该参数
        /// </summary>
        /// <param name="paramname">参数名</param>
        /// <param name="paramvalue">参数值</param>
        /// <returns>结果</returns>
        public bool EditSystemParam(string paramname, string paramvalue)
        {
            bool ans = false;
            try
            {
                string strSql = "select count(*) from SystemParam_t where ParamName='" + paramname + "'";
                object obj = ExecuteScalar(strSql);
                if (obj != null)
                {
                    int n = (int)obj;
                    if (n == 1)
                    {
                        strSql = "update SystemParam_t set ParamValue='" + paramvalue + "' where ParamName='" + paramname + "'";
                        ans = ExecuteNonQuery(strSql);
                    }
                    if (n == 0)
                    {
                        strSql = "insert into SystemParam_t(ParamValue,ParamName) values('" + paramvalue + "','" + paramname + "')";
                        ans = ExecuteNonQuery(strSql);
                    }
                }
            }
            catch (Exception er)
            {
                ans = false;
                LogTool.log.Info("DBHelper.EditSystemParam()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 保存参数,没有时则添加该参数，paramnames[0]为where条件字段paramvalues[0]为where条件值
        /// </summary>
        /// <param name="paramname">参数名</param>
        /// <param name="paramvalue">参数值</param>
        /// <returns>结果</returns>
        public bool EditorAddParam(string tablename,string[] paramnames, string[] paramvalues)
        {
            bool ans = false;
            try
            {
                if ((tablename != "") && (paramnames != null) && (paramvalues != null) && (paramnames.Length == paramvalues.Length) && (paramvalues.Length>0))
                {

                    string strSql = "select count(*) from " + tablename + " where " + paramnames[0] + "='" + paramvalues[0] + "'";
                    object obj = ExecuteScalar(strSql);
                    if (obj != null)
                    {
                        int n = (int)obj;
                        if (n == 1)
                        {
                            strSql = "update " + tablename + " set "; 
                            for (int i = 0; i < paramnames.Length; i++)
                            {
                                if (i == paramnames.Length - 1)
                                {
                                    strSql += (paramnames[i] + "='" + paramvalues[i] + "' where " + paramnames[0] + "='" + paramvalues[0] + "'");
                                }
                                else
                                {
                                    strSql += (paramnames[i] + "='" + paramvalues[i] + "',");
                                }
                            }
                            ans = ExecuteNonQuery(strSql);
                        }
                        if (n == 0)
                        {
                            strSql = "insert into " + tablename + " ("; 
                            for (int i = 0; i < paramnames.Length; i++)
                            {
                                if (i == paramnames.Length - 1)
                                {
                                    strSql += (paramnames[i] + ") values('");
                                }
                                else
                                {
                                    strSql += (paramnames[i] +",");
                                }
                            }
                            for (int i = 0; i < paramvalues.Length; i++)
                            {
                                if (i == paramvalues.Length - 1)
                                {
                                    strSql += (paramvalues[i] + "')");
                                }
                                else
                                {
                                    strSql += (paramvalues[i] + "','");
                                }
                            }
                            ans = ExecuteNonQuery(strSql);
                        }
                    }
                }
            }
            catch (Exception er)
            {
                ans = false;
                LogTool.log.Info("DBHelper.EditorAddParam()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 删除数据库记录
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="condition">删除条件</param>
        /// <returns>结果</returns>
        public bool Delete(string tablename, string condition)
        {
            bool ans = false;
            try
            {
                string strSql = "delete from " + tablename + " where " + condition;
                ans = ExecuteNonQuery(strSql);
            }
            catch (Exception er)
            {
                ans = false;
                LogTool.log.Info("DBHelper.Delete()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 销毁
        /// </summary>
        public void Dispose()
        {
            try
            {
                if (m_connection != null)
                {
                    m_connection.Close();
                    m_connection.Dispose();
                }
            }
            catch { }
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>结果</returns>
        private bool ExecuteNonQuery(string sql)
        {
            bool ans = false;
            OleDbCommand cmd = null;
            try
            {
                if (m_connection != null)
                {
                    cmd = m_connection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    if (m_connection.State != ConnectionState.Open) m_connection.Open();
                    int flag = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    ans = (flag > 0);
                }
            }
            catch (Exception er)
            {
                ans = false;
                if (cmd != null) cmd.Dispose();
                LogTool.log.Info("DBHelper.ExecuteNonQuery()" + er.Message);
            }
            return ans;
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sql">条件</param>
        /// <returns>结果</returns>
        private object ExecuteScalar(string sql)
        {
            object ans = null;
            OleDbCommand cmd = null;
            try
            {
                if (m_connection != null)
                {
                    cmd = m_connection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    if (m_connection.State != ConnectionState.Open) m_connection.Open();
                    ans = cmd.ExecuteScalar();
                }
            }
            catch (Exception er)
            {
                ans = null;
                if (cmd != null) cmd.Dispose();
                LogTool.log.Info("DBHelper.ExecuteScalar()" + er.Message);
            }
            return ans;
        }

        
    }
}
